package extractorCode;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.util.Locale;

import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.UnderlineStyle;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class writeExcel {
	private WritableCellFormat timesBoldUnderline;
	private WritableCellFormat times;
	private String inputFile;
	
public void setOutputFile(String inputFile) {
	this.inputFile = inputFile;
	}

 public WritableWorkbook createBook(int n)
 {
	 
	 //now open a workbook with link(n) as name
	 WorkbookSettings wbSettings = new WorkbookSettings();

		wbSettings.setLocale(new Locale("en", "EN"));
		WritableWorkbook workbook = null;

		try {
			workbook = Workbook.createWorkbook(new File("c:\\newData2\\link"+n+".xls"));
			
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	 return workbook;
 }
	public void write() throws IOException, WriteException {
	File file = new File(inputFile);
		WorkbookSettings wbSettings = new WorkbookSettings();

		wbSettings.setLocale(new Locale("en", "EN"));

		WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
		workbook.createSheet("Report", 0);
		WritableSheet excelSheet = workbook.getSheet(0);
		createLabel(excelSheet);
		createContent(excelSheet);

		workbook.write();
		workbook.close();
	}

	private void createLabel(WritableSheet sheet)
			throws WriteException {
		// Lets create a times font
		WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
		// Define the cell format
		times = new WritableCellFormat(times10pt);
		// Lets automatically wrap the cells
		times.setWrap(true);

		// Create create a bold font with unterlines
	/*	WritableFont times10ptBoldUnderline = new WritableFont(
				WritableFont.TIMES, 10, WritableFont.BOLD, false,
				UnderlineStyle.SINGLE);
		timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
		// Lets automatically wrap the cells
		timesBoldUnderline.setWrap(true);*/

		CellView cv = new CellView();
		cv.setFormat(times);
		//cv.setFormat(timesBoldUnderline);
		cv.setAutosize(true);

		// Write a few headers
		addCaption(sheet, 0, 0, "Header 1");
		addCaption(sheet, 1, 0, "This is another header");
		

	}

	private void createContent(WritableSheet sheet) throws WriteException,
			RowsExceededException {
		// Write a few number
		for (int i = 1; i < 10; i++) {
			// First column
			addNumber(sheet, 0, i, i + 10);
			// Second column
			addNumber(sheet, 1, i, i * i);
		}
		// Lets calculate the sum of it
		StringBuffer buf = new StringBuffer();
		buf.append("SUM(A2:A10)");
		Formula f = new Formula(0, 10, buf.toString());
		sheet.addCell(f);
		buf = new StringBuffer();
		buf.append("SUM(B2:B10)");
		f = new Formula(1, 10, buf.toString());
		sheet.addCell(f);

		// Now a bit of text
		for (int i = 12; i < 20; i++) {
			// First column
			addLabel(sheet, 0, i, "Boring text " + i);
			// Second column
			addLabel(sheet, 1, i, "Another text");
		}
	}

	private void addCaption(WritableSheet sheet, int column, int row, String s)
			throws RowsExceededException, WriteException {
		Label label;
		label = new Label(column, row, s, times);
		sheet.addCell(label);
	}

	private void addNumber(WritableSheet sheet, int column, int row,
			Integer integer) throws WriteException, RowsExceededException {
		Number number;
		WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
		times = new WritableCellFormat(times10pt);
		// Lets automatically wrap the cells
		times.setWrap(false);
		CellView cv = new CellView();
		cv.setFormat(times);
		//cv.setFormat(timesBoldUnderline);
		cv.setAutosize(true);

		number = new Number(column, row, integer, times);
		sheet.addCell(number);
	}
	private void addLabel(WritableSheet sheet, int column, int row, String s)
			throws WriteException, RowsExceededException {
		Label label;
		WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
		times = new WritableCellFormat(times10pt);
		// Lets automatically wrap the cells
		times.setWrap(false);
		CellView cv = new CellView();
		cv.setFormat(times);
		//cv.setFormat(timesBoldUnderline);
		cv.setAutosize(true);
        sheet.setColumnView(column, 100);
		label = new Label(column, row, s, times);
		sheet.addCell(label);
	}
	private void addLink(WritableSheet sheet, int column, int row, String s)
			throws WriteException, RowsExceededException {
		Label label;
		WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
		times = new WritableCellFormat(times10pt);
		// Lets automatically wrap the cells
		times.setWrap(false);
		CellView cv = new CellView();
		cv.setFormat(times);
		//cv.setFormat(timesBoldUnderline);
		cv.setAutosize(true);
        sheet.setColumnView(column, 100);
		label = new Label(column, row, s, times);
		sheet.addCell(label);
	}
	
	private void addDate(WritableSheet sheet, int column, int row, String s)
			throws WriteException, RowsExceededException {
		Label label;
		WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
		times = new WritableCellFormat(times10pt);
		// Lets automatically wrap the cells
		times.setWrap(false);
		CellView cv = new CellView();
		cv.setFormat(times);
		//cv.setFormat(timesBoldUnderline);
		cv.setAutosize(true);
        //sheet.setColumnView(column, 100);
		label = new Label(column, row, s, times);
		sheet.addCell(label);
	}

	public static void main(String[] args) throws WriteException, IOException {
		
		//go in the link1-links folder 
		 BufferedReader reader= new BufferedReader(new FileReader("c:\\newData2\\keywords-link5-excel.txt"));
		 //BufferedReader reader3= new BufferedReader(new FileReader("c:\\newData2\\keywords-link1-excel.txt"));
		 String info;
		 //open a work book here
            writeExcel obj= new writeExcel();
			WritableWorkbook  wrkbook = obj.createBook(1);
            int sheetNo=0;
		 while((info=reader.readLine())!=null)
		 {
			 
			 String[] splitarr= info.split("\t");
			 String keyword= splitarr[0];
			 //now open the file for that keyword.
			 BufferedReader reader2= new BufferedReader(new FileReader("c:\\newData2\\link5-links\\link5-"+keyword+".txt"));
			 //create a new sheet for the keyword here
			 wrkbook.createSheet(keyword, sheetNo);
			 String line;
			 int count=1,row=1,column=1;
			 while((line=reader2.readLine())!=null)
			 {
				 column=1;
				 String [] splitLine= line.split("\t");
				 String link,date;
				 if(splitLine.length>1)
				 {link= splitLine[0].replace("href=\"", "");
				   link= link.replace("\"","");
				  date= splitLine[1];
				  
				 }
				 else
				  {  
					  link= splitLine[0].replace("href=\"", "");
					   link= link.replace("\"","");
				      date="";
				  
				  }
				  
				 //now add the count at row1,column1 link row 1 column 1+1 and date row column 1+2
				 WritableSheet excelSheet= wrkbook.getSheet(sheetNo);
				 obj.addNumber(excelSheet, column, row, count);
				 obj.addLink(excelSheet, column+2, row, link);
				 obj.addDate(excelSheet, column+3, row, date);
				 count++;row++;
			 }
			 sheetNo++;
		 }

		 /*while((info=reader3.readLine())!=null)
		 {
			 
			 String[] splitarr= info.split("\t");
			 String keyword= splitarr[0];
			 //now open the file for that keyword.
			 BufferedReader reader4= new BufferedReader(new FileReader("c:\\newData2\\link2-links\\link2-rem-"+keyword+".txt"));
			 //create a new sheet for the keyword here
			 wrkbook.createSheet("rem-"+keyword, sheetNo);
			 String line;
			 int count=1,row=1,column=1;
			 while((line=reader4.readLine())!=null)
			 {
				 column=1;
				 String [] splitLine= line.split("\t");
				 String link,date;
				 if(splitLine.length>1)
				 {link= splitLine[0].replace("href=\"", "");
				   link= link.replace("\"","");
				  date= splitLine[1];
				  
				 }
				 else
				  {  
					  link= splitLine[0].replace("href=\"", "");
					   link= link.replace("\"","");
				      date="";
				  
				  }
				  
				 //now add the count at row1,column1 link row 1 column 1+1 and date row column 1+2
				 WritableSheet excelSheet= wrkbook.getSheet(sheetNo);
				 obj.addNumber(excelSheet, column, row, count);
				 obj.addLink(excelSheet, column+2, row, link);
				 obj.addDate(excelSheet, column+3, row, date);
				 count++;row++;
			 }
			 sheetNo++;
		 }*/

		 wrkbook.write();
		 wrkbook.close(); 
	}
}
